{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create Tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from google.cloud.exceptions import NotFound\n",
    "from google.cloud import bigquery\n",
    "import pandas as pd\n",
    "import logging\n",
    "import os "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Setup logging"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "logger = logging.getLogger(__name__)\n",
    "logger.setLevel(logging.DEBUG)\n",
    "logger.addHandler(logging.StreamHandler())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Essentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "SERVICE_ACCOUNT_CREDENTIALS = './../credentials/vai-key.json'\n",
    "os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = SERVICE_ACCOUNT_CREDENTIALS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "client = bigquery.Client()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Create dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "arun-genai-bb.flight_reservations\n"
     ]
    }
   ],
   "source": [
    "dataset_id = f\"{client.project}.flight_reservations\"\n",
    "logger.info(dataset_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Dataset arun-genai-bb.flight_reservations created.\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    dataset = client.get_dataset(dataset_id)\n",
    "    logger.info(f\"Dataset {dataset_id} already exists!\")\n",
    "except NotFound:\n",
    "    dataset = bigquery.Dataset(dataset_id)\n",
    "    dataset.location = \"US\"\n",
    "    dataset = client.create_dataset(dataset)\n",
    "    logger.info(f\"Dataset {dataset_id} created.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Create tables "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Create `customers` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "arun-genai-bb.flight_reservations.customers\n"
     ]
    }
   ],
   "source": [
    "table_id = f\"{dataset_id}.customers\"\n",
    "logger.info(table_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers_schema = [\n",
    "    bigquery.SchemaField(\"customer_id\", \"INT64\", mode=\"REQUIRED\"),\n",
    "    bigquery.SchemaField(\"first_name\", \"STRING\", mode=\"REQUIRED\"),\n",
    "    bigquery.SchemaField(\"last_name\", \"STRING\", mode=\"REQUIRED\"),\n",
    "    bigquery.SchemaField(\"email\", \"STRING\", mode=\"REQUIRED\"),\n",
    "    bigquery.SchemaField(\"date_of_birth\", \"DATE\", mode=\"REQUIRED\"),\n",
    "    bigquery.SchemaField(\"created_at\", \"DATETIME\", mode=\"REQUIRED\"),\n",
    "]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Table arun-genai-bb.flight_reservations.customers created.\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    customers_table = client.get_table(table_id)\n",
    "    logger.info(f\"Table {table_id} already exists!\")\n",
    "except NotFound:\n",
    "    customers_table = bigquery.Table(table_id, schema=customers_schema)\n",
    "    customers_table = client.create_table(customers_table)  \n",
    "    logger.info(f\"Table {table_id} created.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Create `flights` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "arun-genai-bb.flight_reservations.flights\n"
     ]
    }
   ],
   "source": [
    "table_id = f\"{dataset_id}.flights\"\n",
    "logger.info(table_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights_schema = [\n",
    "    bigquery.SchemaField(\"flight_id\", \"INT64\", mode=\"REQUIRED\"),\n",
    "    bigquery.SchemaField(\"origin\", \"STRING\", mode=\"REQUIRED\"),\n",
    "    bigquery.SchemaField(\"destination\", \"STRING\", mode=\"REQUIRED\"),\n",
    "    bigquery.SchemaField(\"departure_datetime\", \"DATETIME\", mode=\"REQUIRED\"),\n",
    "    bigquery.SchemaField(\"arrival_datetime\", \"DATETIME\", mode=\"REQUIRED\"),\n",
    "    bigquery.SchemaField(\"carrier\", \"STRING\", mode=\"REQUIRED\"),\n",
    "    bigquery.SchemaField(\"price\", \"FLOAT64\", mode=\"REQUIRED\"),\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Table arun-genai-bb.flight_reservations.flights created.\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    flights_table = client.get_table(table_id)\n",
    "    logger.info(f\"Table {table_id} already exists!\")\n",
    "except NotFound:\n",
    "    flights_table = bigquery.Table(table_id, schema=flights_schema)\n",
    "    flights_table = client.create_table(flights_table)  \n",
    "    logger.info(f\"Table {table_id} created.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Create `reservations` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "arun-genai-bb.flight_reservations.reservations\n"
     ]
    }
   ],
   "source": [
    "table_id = f\"{dataset_id}.reservations\"\n",
    "logger.info(table_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "reservations_schema = [\n",
    "    bigquery.SchemaField(\"reservation_id\", \"INT64\", mode=\"REQUIRED\"),\n",
    "    bigquery.SchemaField(\"customer_id\", \"INT64\", mode=\"REQUIRED\"),\n",
    "    bigquery.SchemaField(\"flight_id\", \"INT64\", mode=\"REQUIRED\"),\n",
    "    bigquery.SchemaField(\"reservation_datetime\", \"DATETIME\", mode=\"REQUIRED\"),\n",
    "    bigquery.SchemaField(\"status\", \"STRING\", mode=\"REQUIRED\"),\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Table arun-genai-bb.flight_reservations.reservations created.\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    reservations_table = client.get_table(table_id)\n",
    "    logger.info(f\"Table {table_id} already exists!\")\n",
    "except NotFound:\n",
    "    reservations_table = bigquery.Table(table_id, schema=reservations_schema)\n",
    "    reservations_table = client.create_table(reservations_table)  \n",
    "    logger.info(f\"Table {table_id} created.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Create `transactions` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "arun-genai-bb.flight_reservations.transactions\n"
     ]
    }
   ],
   "source": [
    "table_id = f\"{dataset_id}.transactions\"\n",
    "logger.info(table_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "transactions_schema = [\n",
    "    bigquery.SchemaField(\"transaction_id\", \"INT64\", mode=\"REQUIRED\"),\n",
    "    bigquery.SchemaField(\"reservation_id\", \"INT64\", mode=\"REQUIRED\"),\n",
    "    bigquery.SchemaField(\"amount\", \"FLOAT64\", mode=\"REQUIRED\"),\n",
    "    bigquery.SchemaField(\"transaction_datetime\", \"DATETIME\", mode=\"REQUIRED\"),\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Table arun-genai-bb.flight_reservations.transactions created.\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    transactions_table = client.get_table(table_id)\n",
    "    logger.info(f\"Table {table_id} already exists!\")\n",
    "except NotFound:\n",
    "    transactions_table = bigquery.Table(table_id, schema=transactions_schema)\n",
    "    transactions_table = client.create_table(transactions_table)  \n",
    "    logger.info(f\"Table {table_id} created.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Populate tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "      <th>date_of_birth</th>\n",
       "      <th>created_at</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "      <td>Doe</td>\n",
       "      <td>john.doe@example.com</td>\n",
       "      <td>1985-02-15</td>\n",
       "      <td>2021-01-01 08:30:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Jane</td>\n",
       "      <td>Doe</td>\n",
       "      <td>jane.doe@example.com</td>\n",
       "      <td>1987-05-23</td>\n",
       "      <td>2021-01-15 12:45:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Alice</td>\n",
       "      <td>Johnson</td>\n",
       "      <td>alice.j@example.com</td>\n",
       "      <td>1990-08-10</td>\n",
       "      <td>2021-03-20 09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Bob</td>\n",
       "      <td>Smith</td>\n",
       "      <td>bob.smith@example.com</td>\n",
       "      <td>1982-11-12</td>\n",
       "      <td>2021-02-25 10:15:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Charlie</td>\n",
       "      <td>Brown</td>\n",
       "      <td>charlie.b@example.com</td>\n",
       "      <td>1995-05-20</td>\n",
       "      <td>2021-06-01 11:30:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id first_name last_name                  email date_of_birth  \\\n",
       "0            1       John       Doe   john.doe@example.com    1985-02-15   \n",
       "1            2       Jane       Doe   jane.doe@example.com    1987-05-23   \n",
       "2            3      Alice   Johnson    alice.j@example.com    1990-08-10   \n",
       "3            4        Bob     Smith  bob.smith@example.com    1982-11-12   \n",
       "4            5    Charlie     Brown  charlie.b@example.com    1995-05-20   \n",
       "\n",
       "            created_at  \n",
       "0  2021-01-01 08:30:00  \n",
       "1  2021-01-15 12:45:00  \n",
       "2  2021-03-20 09:00:00  \n",
       "3  2021-02-25 10:15:00  \n",
       "4  2021-06-01 11:30:00  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('./../data/customers.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = [tuple(row) for row in df.to_numpy()]\n",
    "client.insert_rows(customers_table, data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>flight_id</th>\n",
       "      <th>origin</th>\n",
       "      <th>destination</th>\n",
       "      <th>departure_datetime</th>\n",
       "      <th>arrival_datetime</th>\n",
       "      <th>carrier</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>JFK</td>\n",
       "      <td>LAX</td>\n",
       "      <td>2023-11-05 09:00:00</td>\n",
       "      <td>2023-11-05 12:30:00</td>\n",
       "      <td>Delta</td>\n",
       "      <td>450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>LAX</td>\n",
       "      <td>JFK</td>\n",
       "      <td>2023-11-10 15:00:00</td>\n",
       "      <td>2023-11-10 23:30:00</td>\n",
       "      <td>Delta</td>\n",
       "      <td>450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>JFK</td>\n",
       "      <td>ATL</td>\n",
       "      <td>2023-11-15 07:00:00</td>\n",
       "      <td>2023-11-15 09:30:00</td>\n",
       "      <td>Southwest</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>ATL</td>\n",
       "      <td>LAX</td>\n",
       "      <td>2023-11-18 17:00:00</td>\n",
       "      <td>2023-11-18 19:30:00</td>\n",
       "      <td>Southwest</td>\n",
       "      <td>400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>LAX</td>\n",
       "      <td>SEA</td>\n",
       "      <td>2023-11-22 14:00:00</td>\n",
       "      <td>2023-11-22 16:30:00</td>\n",
       "      <td>United</td>\n",
       "      <td>350</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   flight_id origin destination   departure_datetime     arrival_datetime  \\\n",
       "0          1    JFK         LAX  2023-11-05 09:00:00  2023-11-05 12:30:00   \n",
       "1          2    LAX         JFK  2023-11-10 15:00:00  2023-11-10 23:30:00   \n",
       "2          3    JFK         ATL  2023-11-15 07:00:00  2023-11-15 09:30:00   \n",
       "3          4    ATL         LAX  2023-11-18 17:00:00  2023-11-18 19:30:00   \n",
       "4          5    LAX         SEA  2023-11-22 14:00:00  2023-11-22 16:30:00   \n",
       "\n",
       "     carrier  price  \n",
       "0      Delta    450  \n",
       "1      Delta    450  \n",
       "2  Southwest    300  \n",
       "3  Southwest    400  \n",
       "4     United    350  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('./../data/flights.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = [tuple(row) for row in df.to_numpy()]\n",
    "client.insert_rows(flights_table, data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>reservation_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>flight_id</th>\n",
       "      <th>reservation_datetime</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2023-10-01 10:30:00</td>\n",
       "      <td>Confirmed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2023-10-02 11:00:00</td>\n",
       "      <td>Confirmed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2023-10-03 12:15:00</td>\n",
       "      <td>Confirmed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2023-10-05 08:30:00</td>\n",
       "      <td>Cancelled</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2023-10-06 14:20:00</td>\n",
       "      <td>Confirmed</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   reservation_id  customer_id  flight_id reservation_datetime     status\n",
       "0               1            1          1  2023-10-01 10:30:00  Confirmed\n",
       "1               2            2          2  2023-10-02 11:00:00  Confirmed\n",
       "2               3            3          3  2023-10-03 12:15:00  Confirmed\n",
       "3               4            3          4  2023-10-05 08:30:00  Cancelled\n",
       "4               5            6          4  2023-10-06 14:20:00  Confirmed"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('./../data/reservations.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = [tuple(row) for row in df.to_numpy()]\n",
    "client.insert_rows(reservations_table, data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>transaction_id</th>\n",
       "      <th>reservation_id</th>\n",
       "      <th>amount</th>\n",
       "      <th>transaction_datetime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>450</td>\n",
       "      <td>2023-10-01 10:31:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>450</td>\n",
       "      <td>2023-10-02 11:01:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>300</td>\n",
       "      <td>2023-10-03 12:16:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>350</td>\n",
       "      <td>2023-10-06 14:21:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>550</td>\n",
       "      <td>2023-10-10 10:01:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   transaction_id  reservation_id  amount transaction_datetime\n",
       "0               1               1     450  2023-10-01 10:31:00\n",
       "1               2               2     450  2023-10-02 11:01:00\n",
       "2               3               3     300  2023-10-03 12:16:00\n",
       "3               5               5     350  2023-10-06 14:21:00\n",
       "4               6               6     550  2023-10-10 10:01:00"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('./../data/transactions.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = [tuple(row) for row in df.to_numpy()]\n",
    "client.insert_rows(transactions_table, data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note:** Populating tables in BigQuery can take time, typically ranging between approximately 60 to 90 mins."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".bq-sql-agent",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
